package com.zhuoshi.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.zhuoshi.bean.CustomerBean;
import com.zhuoshi.utils.DbHelper;

/**
 * 客户及联系人 数据处理
 * 
 * @author Administrator
 *
 */
public class CustomerDao {
	// 添加
	public int add(CustomerBean bean) {
		Connection conn = null;
		PreparedStatement pstat = null;
		int rows = 0;
		try {
			conn = DbHelper.getConnection();
			StringBuffer sql = new StringBuffer();
			sql.append("insert into customer (custname,abbreviation,constracts,dept,quarters) values");
			sql.append(" (?,?,?,?,?)");
			pstat = conn.prepareStatement(sql.toString());
			pstat.setString(1, bean.getCustname());
			pstat.setString(2, bean.getAbbreviation());
			pstat.setString(3, bean.getConstracts());
			pstat.setString(4, bean.getDept());
			pstat.setString(5, bean.getQuarters());
			rows = pstat.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DbHelper.dispose(conn, pstat, null);
		}
		
		return rows;
	}
	// 修改
	public int update(CustomerBean bean) {
		Connection conn = null;
		PreparedStatement pstat = null;
		int rows = 0;
		try {
			conn = DbHelper.getConnection();
			StringBuffer sql = new StringBuffer();
			sql.append("update customer set ");
			sql.append("custname=?,");
			sql.append("abbreviation=?,");
			sql.append("constracts=?,");
			sql.append("dept=?,");
			sql.append("quarters=? ");
			sql.append(" where id=?");
			pstat = conn.prepareStatement(sql.toString());
			pstat.setString(1, bean.getCustname());
			pstat.setString(2, bean.getAbbreviation());
			pstat.setString(3, bean.getConstracts());
			pstat.setString(4, bean.getDept());
			pstat.setString(5, bean.getQuarters());
			pstat.setInt(6, bean.getId());
			rows = pstat.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DbHelper.dispose(conn, pstat, null);
		}
		
		return rows;
	}

	// 删除
	public int delete(int id) {
		Connection conn = null;
		PreparedStatement pstat = null;
		int rows = 0;
		try {
			conn = DbHelper.getConnection();
			StringBuffer sql = new StringBuffer();
			sql.append("delete from customer where id=?");
			pstat = conn.prepareStatement(sql.toString());
			pstat.setInt(1,id);
			rows = pstat.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DbHelper.dispose(conn, pstat, null);
		}
		
		return rows;
	}
	/**
	 * 通过id查询 获取一个数据实体
	 * @param id 自增ID
	 * @return
	 */
	public CustomerBean getModel(int id){
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		CustomerBean bean = null;
		try {
			conn = DbHelper.getConnection();
			StringBuffer sql = new StringBuffer();
			sql.append("select * from customer where id=?");
			pstat = conn.prepareStatement(sql.toString());
			pstat.setInt(1, id);
			rs = pstat.executeQuery();
			while(rs.next()){
				bean = new CustomerBean();
				bean.setId(rs.getInt("id"));
				bean.setCustname(rs.getString("custname"));
				bean.setAbbreviation(rs.getString("abbreviation"));
				bean.setDept(rs.getString("dept"));
				bean.setConstracts(rs.getString("constracts"));
				bean.setQuarters(rs.getString("quarters"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DbHelper.dispose(conn, pstat, rs);
		}
		return bean;
	}
	
	
	
	/**
	 * 查询多条 List 
	 * @param bean 条件  如果没有条件 传入null 查询所有
	 * @return
	 */
	public List<CustomerBean> getList(CustomerBean bean){
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<CustomerBean> list = new ArrayList<CustomerBean>();
		try {
			conn = DbHelper.getConnection();
			//---===================构建查询条件=================---//
			StringBuffer sql = new StringBuffer();
			sql.append("select * from customer where 1=1");
			//if(bean!=null){
//			if(!"".equals(bean.getCustname()) && bean.getCustname()!=null){//客户名称查询
//			sql.append(" and custname like '%"+bean.getCustname()+"%'");
//			}
			if(!"".equals(bean.getAbbreviation()) && bean.getAbbreviation()!=null){//客户简称查询
				sql.append(" and abbreviation like '%"+bean.getAbbreviation()+"%'");
				}
			if(!"".equals(bean.getConstracts()) && bean.getConstracts()!=null){//联系人查询
				sql.append(" and constracts like '%"+bean.getConstracts()+"%'");
				}
			if(!"".equals(bean.getDept()) && bean.getDept()!=null){//所属部门查询
				sql.append(" and dept like '%"+bean.getDept()+"%'");
				}
//			if(!"".equals(bean.getQuarters()) && bean.getQuarters()!=null){//岗位查询
//				sql.append(" and quarters like '%"+bean.getQuarters()+"%'");
//				}
			//}
			//---===================/构建查询条件=================---//
			pstat = conn.prepareStatement(sql.toString());
//			System.out.println(sql);
			rs = pstat.executeQuery();
			while(rs.next()){
				CustomerBean cBean = new CustomerBean();
				cBean.setId(rs.getInt("id"));
				cBean.setCustname(rs.getString("custname"));
				cBean.setAbbreviation(rs.getString("abbreviation"));
				cBean.setConstracts(rs.getString("constracts"));
				cBean.setDept(rs.getString("dept"));
				cBean.setQuarters(rs.getString("quarters"));
				list.add(cBean);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DbHelper.dispose(conn, pstat, rs);
		}
		return list;
	}
	
	/**
	 * 查询所有
	 * @return
	 */
	public List<CustomerBean> getAllList(){
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<CustomerBean> list = new ArrayList<CustomerBean>();
		try {
			conn = DbHelper.getConnection();
			//---===================构建查询条件=================---//
			StringBuffer sql = new StringBuffer();
			sql.append("select * from customer where 1=1");
			
			//---===================/构建查询条件=================---//
			pstat = conn.prepareStatement(sql.toString());
//			System.out.println(sql);
			rs = pstat.executeQuery();
			while(rs.next()){
				CustomerBean cBean = new CustomerBean();
				cBean.setId(rs.getInt("id"));
				cBean.setCustname(rs.getString("custname"));
				cBean.setAbbreviation(rs.getString("abbreviation"));
				cBean.setConstracts(rs.getString("constracts"));
				cBean.setDept(rs.getString("dept"));
				cBean.setQuarters(rs.getString("quarters"));
				list.add(cBean);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DbHelper.dispose(conn, pstat, rs);
		}
		return list;
	}
	
}
